package com.columbia;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * User: csavard
 * Date: 13-11-24
 */
public class CommandGenerator {

    private InputStream commandesClientInput;
    private InputStream commandesColumbiaInput;
    private String outFile;
    private int ligne1;
    private int ligne2;
    private String col1;
    private String col2;

    /**
     * @param commandesClientInput   Le fichier de commande client
     * @param commandesColumbiaInput Le fichier de commande Columbia
     * @param outFile                Le fichier ou les donn/es seront inscrites
     * @param ligne1                 premiere ligne ou il y a des donnee. 1 à l'infini
     * @param ligne2                 derniere ligne ou il y a des donnee. ligne1 à l'infini
     * @param col1                   la première colonne du magasin
     * @param col2                   la dernière colonne du magasin
     * @return
     */
    public CommandGenerator(InputStream commandesClientInput, InputStream commandesColumbiaInput, String outFile, int ligne1, int ligne2, String col1, String col2) {
        this.commandesClientInput = commandesClientInput;
        this.commandesColumbiaInput = commandesColumbiaInput;
        this.outFile = outFile;
        this.ligne1 = ligne1;
        this.ligne2 = ligne2;
        this.col1 = col1;
        this.col2 = col2;
    }


    public List<Item> extraireLesItems() throws IOException {
        List<Item> items = new ArrayList<>();


        Workbook wb = new HSSFWorkbook(commandesClientInput);

        //Les lignes ont un offset de 1.
        ligne1 -= 1;
        ligne2 -= 1;

        int noCol1 = ExcelUtil.convertColStringToIndex(col1);
        int noCol2 = ExcelUtil.convertColStringToIndex(col2);

        int numberOfSheets = wb.getNumberOfSheets();
        for (int sheetIndex = 1; sheetIndex < numberOfSheets; sheetIndex++) {
            HSSFSheet sheet = (HSSFSheet) wb.getSheetAt(sheetIndex);
            for (int itemIndex = ligne1; itemIndex <= ligne2; itemIndex++) {
                HSSFRow row = sheet.getRow(itemIndex);
                HSSFCell codeCell = row.getCell(0);

                String code = getCleanCode(ExcelUtil.getStringValue(codeCell));
                if (code != null) {
                    HSSFCell colorCodeCell = row.getCell(1);
                    String colorCode = getCleanCode(ExcelUtil.getStringValue(colorCodeCell));

                    Item item = new Item();
                    item.setCode(code);
                    item.setCodeCouleur(colorCode);

                    for (int colGrandeur = noCol1; colGrandeur <= noCol2; colGrandeur++) {
                        HSSFCell colGrandeurCell = row.getCell(colGrandeur);
                        String nbItemsStr = ExcelUtil.getStringValue(colGrandeurCell);
                        if (nbItemsStr != null) {
                            int nbItems = (int) Float.parseFloat(nbItemsStr);
                            if (nbItems > 0) {
                                String grandeur = getGrandeur(colGrandeur, sheet);
                                item.ajouter(grandeur, nbItems);
                            }
                        }
                    }

                    items.add(item);
                }
            }
        }

        return items;
    }

    public void generationCommandesColumbia() throws IOException, InvalidFormatException {
        FileWriter fileWriter = new FileWriter(outFile);

        List<Item> items = extraireLesItems();
        HSSFWorkbook wb = (HSSFWorkbook) WorkbookFactory.create(POIFSFileSystem.createNonClosingInputStream(commandesColumbiaInput));
        int itemIndex = 1;
        for (Item item : items) {
            boolean found = false;
            for (int sheetIdex = 1; sheetIdex < 5 && !found; sheetIdex++) {
                HSSFSheet sheet = wb.getSheetAt(sheetIdex);
                for (int rowIndex = 0; rowIndex < sheet.getLastRowNum() && !found; rowIndex++) {
                    HSSFRow row = sheet.getRow(rowIndex);
                    HSSFCell cell = row.getCell(1);
                    String fullCode = getCleanCode(ExcelUtil.getStringValue(cell));
                    if (fullCode != null && fullCode.equals(item.getFullCode())) {
                        found = true;
                        System.out.println(itemIndex + ". " + item.getFullCode() + " " + sheet.getSheetName() + " " + (rowIndex + 1));
                        Map<String, Integer> nombreParGrandeur = item.getNombreParGrandeur();
                        if (nombreParGrandeur != null) {
                            List<String> newData = new ArrayList<>();
                            newData.add(sheet.getSheetName());
                            newData.add(String.valueOf(rowIndex + 1));

                            Set<String> grandeurs = nombreParGrandeur.keySet();
                            for (String grandeur : grandeurs) {
                                HSSFCell grandeurCell = getGrandeurCell(row, grandeur);
                                if (grandeurCell != null) {
                                    newData.add(ExcelUtil.convertIndexToColString(grandeurCell.getColumnIndex()));
                                    newData.add(String.valueOf(nombreParGrandeur.get(grandeur)));
                                }
                            }

                            String delimiter = "";
                            for(String token: newData){
                                fileWriter.write(delimiter + token);
                                delimiter = ",";
                            }
                            fileWriter.write("\r\n");
                        }
                    }
                }
            }

            if (!found) {
                System.out.println(itemIndex + ". L'item: " + item.getFullCode() + " n'a pas été trouvé");
            }

            itemIndex++;
        }

        fileWriter.close();
    }

    private static String getCleanCode(String code) {
        if (code != null && code.lastIndexOf(".") > -1) {
            code = code.substring(0, code.lastIndexOf("."));
        }

        return code;
    }

    private static String getGrandeur(int col, HSSFSheet sheet) {
        String grandeur = null;

        for (int i = 5; i < 8; i++) {
            HSSFRow row = sheet.getRow(i);
            HSSFCell cell = row.getCell(col);
            String grandeurTemp = ExcelUtil.getStringValue(cell);
            if (grandeurTemp != null && !grandeurTemp.equals("0.0")) {
                grandeur = grandeurTemp;
                break;
            }
        }
        return grandeur;
    }

    private static HSSFCell getGrandeurCell(HSSFRow row, String grandeur) {
        HSSFCell cell = null;
        boolean found = false;

        int col1 = ExcelUtil.convertColStringToIndex("J");
        int col2 = ExcelUtil.convertColStringToIndex("Q");

        HSSFCell deliveryCell = row.getSheet().getRow(3).getCell(ExcelUtil.convertColStringToIndex("Z"));
        double delivery = getDelivery(deliveryCell);

        cell = getGrandeurCell(row, grandeur, delivery, col1, col2);
        if (cell == null) {
            col1 = ExcelUtil.convertColStringToIndex("AA");
            col2 = ExcelUtil.convertColStringToIndex("AH");

            deliveryCell = row.getSheet().getRow(3).getCell(ExcelUtil.convertColStringToIndex("AQ"));
            delivery = getDelivery(deliveryCell);

            cell = getGrandeurCell(row, grandeur, delivery, col1, col2);
            if (cell == null) {
                col1 = ExcelUtil.convertColStringToIndex("AR");
                col2 = ExcelUtil.convertColStringToIndex("AY");

                deliveryCell = row.getSheet().getRow(3).getCell(ExcelUtil.convertColStringToIndex("BH"));
                delivery = getDelivery(deliveryCell);

                cell = getGrandeurCell(row, grandeur, delivery, col1, col2);
            }
        }
        return cell;
    }

    private static double getDelivery(HSSFCell deliveryCell) {
        String deliveryStr;
        double delivery = 0.0;
        try {
            deliveryStr = ExcelUtil.getStringValue(deliveryCell);
            delivery = Double.parseDouble(deliveryStr);
        } catch (NumberFormatException e) {
            //C<est normal dans le cas d<un N/A
        }
        return delivery;
    }

    private static HSSFCell getGrandeurCell(HSSFRow row, String grandeur, double delivery, int col1, int col2) {
        HSSFCell cell = null;

        for (int noCol = col1; noCol <= col2 && cell == null; noCol++) {
            HSSFCell dispoCell = row.getCell(ExcelUtil.convertColStringToIndex("BO"));
            HSSFCell grandeurCell = row.getCell(noCol);

            String dispoStr = ExcelUtil.getStringValue(dispoCell);
            double dispo = Double.parseDouble(dispoStr);

            if (!grandeurCell.getCellStyle().getLocked() && delivery >= dispo) {
                String grandeurForCol = getGrandeurForCol(grandeurCell);
                if (grandeurForCol != null && grandeurForCol.equalsIgnoreCase(grandeur)) {
                    cell = grandeurCell;
                }
            }
        }
        return cell;
    }

    private static String getGrandeurForCol(HSSFCell cell) {
        String grandeur = null;
        HSSFSheet sheet = cell.getSheet();
        int noCol = cell.getColumnIndex();
        int noRow = cell.getRowIndex();

        while (grandeur == null && noRow > 0) {
            noRow--;
            HSSFCell tempCell = sheet.getRow(noRow).getCell(noCol);
            if (tempCell.getCellStyle().getLocked()) {
                grandeur = ExcelUtil.getStringValue(tempCell);
            }
        }

        return grandeur;
    }
}
